function f_kill_alter_sql()
{ 
    declare kill_sql alter_t alter_user alter_db get_kill_sql alter_host alter_port fun_name
    alter_host="${1}"
    alter_port="${2}"
    alter_user="${3}"
    alter_t="${4}"
    fun_name="f_kill_alter_sql"
    kill_sql="select concat('kill ',id,';') from ${isdbp} where time > 10 and (state like '%altering table%' or state like '%copy to tmp table%') 
        and info like '%alter table ${alter_t}%' and user = '${alter_user}';"

    get_kill_sql="$(f_exe_sql_in_mysql "${kill_sql}" "${alter_host}" "${alter_port}" "nodb")"
    if [ ${exe_state} -eq 0 ] && [ -n "${get_kill_sql}" ]
    then #kill掉mysql层的改表进程, 这里是kill掉改表进程
        kill_sql="select * from ${isdbp} where time > 10 and (state like '%altering table%' or state like '%copy to tmp table%') 
            and info like '%alter table ${alter_t}%' and user = '${alter_user}';"
        f_exe_sql_in_mysql "${kill_sql}" "${alter_host}" "${alter_port}" "nodb"|awk -F'\t' '{
                    print "[ ID : "$1" ] [ USER : "$2" ] [ IP : "$3" ] [ DB : "$4" ] [ COMMAND : "$5" ] [ TIME : "$6" ] [ STATE : "$7" ] [ SQL : "$8" ]"}'
        f_logging "REMAIND:${fun_name}" "现在将在 kill 掉如上 alter 操作"
        f_exe_sql_in_mysql "${get_kill_sql}" "${alter_host}" "${alter_port}" "nodb" || { 
            f_logging "ERROR:${fun_name}" "如上操作 kill 失败"
            _1_="执行未知错误. 尝试kill掉alter操作, 但是kill失败, 请联系dba进行处理"
            f_record_info_to_dbadb "alter" "${alter_t}" "${_1_}" || true
            __="insert into ${t_prog_task}(order_id,user_mail,port,table_name,send_state,start_time,remarks) select ${user_order_id},
                '${g_user_mail_addr}',${mysql_port},'${mysql_db}.${alter_t}',0,'$(date -d @${script_start_time} "+%F %H:%M:%S")','${_1_}';"
            f_exe_sql_to_dbadb "${__}" "f_update_status_for_task" || true
        }
    fi

    return 0
}

function f_kill_osc_process()
{   #遇到mdl或者连接数|活跃连接数过高, 需要kill掉改表任务
    declare _t _h _p _d _T _u
    _t="${1}" #osc 工具, ghost还是pt
    _h="${2}" #mysql master ip
    _p="${3}" #mysql端口
    _d="${4}" #正在操作的db
    _T="${5}" #正在改表的表名
    _u="${6}" #改表用户

    for (( check_pid=0;check_pid<30;check_pid++ ))
    do #kill掉os层的改表进程
        osc_is_run="$(ps -ef|grep "${_t}"|grep -w "${_h}"|grep -w "${_p}"|grep -w "${_d}"|grep -wc "${_T}")" || true
        [ "${osc_is_run}x" == "0x" ] && break
        sleep 1
        { ps -ef|grep "${_t}"|grep -w "${_h}"|grep -w "${_p}"|grep -w "${_d}"|grep -w "${_T}"|awk '{print $2}'|xargs -i kill -9 {};} || true
    done

    #如果是master, 则mdl状态一定是改表用户, 如果是从库可能是因为system user, 因为是复制线程
    select_concat_sql="select concat('kill ',id,';') from ${isdbp}"
    select_where_info="where STATE like 'Waiting for table metadata lock%' and user = '${_u}' and db = '${_d}';"

    kill_sql="${select_concat_sql} ${select_where_info}"
    get_kill_sql="$(f_exe_sql_in_mysql "${kill_sql}" "${_h}" "${_p}" "nodb")"

    if [ ${exe_state} -eq 0 ] && [ -n "${get_kill_sql}" ]
    then #kill掉mysql层的改表进程, 这里是kill掉改表进程
        f_exe_sql_in_mysql "${get_kill_sql}" "${_h}" "${_p}" "nodb" && exe_state="${?}" || exe_state="${?}"
    fi

    return ${exe_state}
}

function f_kill_mysql_query()
{   #kill掉mysql连接
    declare _kill_T _kill_t _kill_D _kill_ip _kill_port _kill_type _kill_role _show_info _kill_sql _get_kill_info _get_kill_sql _where_info
    declare -a _select_state
    declare fun_name
    fun_name="f_kill_mysql_query"
    _kill_ip="${1}"
    _kill_port="${2}"
    _kill_type="${3}"
    _kill_role="${4}"
    _show_info="${5}"
    _kill_T="${6}" #tools
    _kill_D="${7}" #db
    _kill_t="${8}" #table
    _kill_sql="select concat('kill ',t2.id,';') from ${isdb}.innodb_trx t1"           #拼接kill sql
    _kill_sql="${_kill_sql} join ${isdbp} t2 on t1.trx_mysql_thread_id = t2.id"       #拼接kill sql
    _get_kill_info="select * from ${isdbp} t2"                                        #查找连接信息的sql 
 
    if [ "${_show_info}x" == "mdlx" ]
    then
        _show_info="存在mdl锁等待"
    elif [ "${_show_info}x" == "connectx" ]
    then
        _show_info="连接数太大, 超过设置的最大连接数${max_threads}"
    else
        _show_info="活跃连接数太大, 超过设置的最大活跃连接数${max_running}"
    fi

    if [ "$(grep -cP "^(drop|rename|truncate)$" <<< "${_kill_T}")x" == "1x" ]
    then #如果是这些操作, 就kill掉所有dml及select查询, 但是有个问题, 如果业务的sql是非格式化的sql, 空格是多个就有问题
        _where_info="where ((t2.info is null and t2.state = '' and t2.command = 'Sleep') and t2.user != 'root') or (t2.db = '${_kill_D}' and"
        _where_info="${_where_info} (t2.info like 'select % from ${_kill_t}' or t2.info like 'select % from ${_kill_t} %' or t2.info like 'insert into "
        _where_info="${_where_info} ${_kill_t} %' or t2.info like 'update ${_kill_t} %' or t2.info like 'delete from ${_kill_t} %'))"
        _where_info="${_where_info} and (t2.info not like '${_kill_T} table ${_kill_t} %' or t2.info not like '${_kill_T} table ${_kill_t};')"
    elif [ "${_kill_type}x" == "selectx" ] && [ "${_kill_role}x" == "masterx" ]
    then
        #这里的数组不能使用关联数组, 定义几种select的状态, master则kill掉超过1800s的select
        _select_state+=("t2.state like 'Creating tmp table%' or")
        _select_state+=("t2.state like 'User sleep%' or")
        _select_state+=("t2.state like 'Sleep%' or")
        _select_state+=("t2.state like 'Sorting for order%' or")
        _select_state+=("t2.state like 'Sorting for group%' or")
        _select_state+=("t2.state like 'Creating sort index%' or")
        _select_state+=("t2.state like 'Copying to group table%' or")
        _select_state+=("t2.state like 'Copying to tmp table on disk%' or")
        _select_state+=("t2.state like 'Creating table%' or")
        _select_state+=("t2.state like 'Sending data%'")
        _where_info="where ((t2.info is null and t2.state = '') or ((${_select_state[@]}) and t2.info like 'select %' and t2.time > 1800))"
        _where_info="${_where_info} and t2.user != 'root' and (t2.db = '${_kill_D}' and (t2.info like 'select % from ${_kill_t}' or t2.info"
        _where_info="${_where_info} like 'select % from ${_kill_t} %'))"
    elif [ "${_kill_type}x" == "selectx" ]
    then #定义 获取 kill select的sql , slave则kill掉超过10s的select
        _select_state+=("t2.state != 'Waiting for table metadata lock'")
        _where_info="where ((t2.info is null and t2.state = '') or ((${_select_state[@]}) and t2.info like 'select %' and t2.time > 10))"
        _where_info="${_where_info} and t2.user != 'root' and (t2.db = '${_kill_D}' and (t2.info like 'select % from ${_kill_t}' or t2.info like"
        _where_info="${_where_info} 'select % from ${_kill_t} %'))"
    else #定义 获取 kill 空连接的sql
        _kill_sql="select concat('kill ',t2.id,';') from ${isdbp} t2" #拼接kill sql
        _where_info="where (t2.info is null and t2.state = '' and t2.command = 'Sleep') and t2.user not in ('root','mydba') and t2.id != connection_id()"
    fi

    _kill_sql="${_kill_sql} ${_where_info}"             #这里有个问题, 因为检查跟kill连接时异步的, 会出现查到了, 但是kill的时候不存在xxx processlist id
    _get_kill_info="${_get_kill_info} ${_where_info}"   #这是正常现象
    _get_kill_sql="$(f_exe_sql_in_mysql "${_kill_sql}" "${_kill_ip}" "${_kill_port}" "slave")"
    f_logging "REMAIND:${fun_name}" "${_kill_role}节点 '${_kill_ip}:${_kill_port}' ${_show_info}, 现在释放${_kill_type}连接" "1" "0" "0"

    if [ -z "${_get_kill_sql}" ]
    then
        f_logging "REMAIND:${fun_name}" "${_kill_role}节点 '${_kill_ip}:${_kill_port}' 没有找到符合 ${_kill_type} 的连接" "1" "0" "0"
        return 1
    fi

    f_logging "REMAIND:${fun_name}" "现在将在 '${_kill_ip}:${_kill_port}' kill掉如下连接" "1" "0" "0"
    f_exe_sql_in_mysql "${_get_kill_info}" "${_kill_ip}" "${_kill_port}" "slave"|awk -F'\t' '{
        print "[ ID : "$1" ] [ USER : "$2" ] [ IP : "$3" ] [ DB : "$4" ] [ COMMAND : "$5" ] [ TIME : "$6" ] [ STATE : "$7" ] [ SQL : "$8" ]"}'
    f_exe_sql_in_mysql "${_get_kill_sql}" "${_kill_ip}" "${_kill_port}" "slave"
    f_logging "REMAIND:${fun_name}" "现在将在 '${_kill_ip}:${_kill_port}' kill掉如上连接" "1" "0" "0"
    return 0
}

